/***********************************************************************************************************************************
 *    Name: TLogInfo.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
	DBCC LOGINFO WITH NO_INFOMSGS;
**/
SET NOCOUNT ON;
CREATE TABLE #LogInfo (
	RecoveryID	TINYINT,
	FileID		TINYINT,
	FileSize	BIGINT,
	StartOffset	BIGINT,
	FSeqNo		INT,
	Status		TINYINT,
	Parity		TINYINT,
	CreateLSN	NUMERIC(25,0)	);
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(32)), 4) IN ('10.5','10.0','9.00') ALTER TABLE #LogInfo DROP COLUMN RecoveryID;
CREATE TABLE #FileInfo (
	db_name		SYSNAME,
	FileID		INT,
	Size		INT,
	Growth		INT,
	IsPercent	BIT	);
BEGIN TRY DROP TABLE #VLFStats END TRY BEGIN CATCH END CATCH;
CREATE TABLE #VLFStats (
	[Database Name]			SYSNAME,
	[File ID]				INT,
	[# of VLFs]				INT,
	[Min VLF Size (MB)]		INT,
	[Avg VLF Size (MB)]		INT,
	[Max VLF Size (MB)]		INT,
	[Total VLF Size (MB)]	INT,
	[Size (MB)]				INT,
	[Growth (MB)]			INT,
	IsPercent				BIT	);

DECLARE
  @tsql_template NVARCHAR(MAX),
  @tsql_command  NVARCHAR(MAX),
  @database_name SYSNAME;
SET @tsql_template= N'USE [?]; INSERT INTO #LogInfo EXECUTE (''DBCC LOGINFO WITH NO_INFOMSGS''); INSERT INTO #FileInfo SELECT DB_NAME(), fileid, size/128, CASE status WHEN 1048642 THEN growth ELSE growth/128 END, CASE status WHEN 1048642 THEN 1 ELSE 0 END FROM sys.sysfiles;';

DECLARE dblist CURSOR LOCAL FAST_FORWARD FOR SELECT database_name FROM master.DBAdmin.DBList(Default);
OPEN dblist;
WHILE (0=0) BEGIN;
  FETCH NEXT FROM dblist INTO @database_name;
  TRUNCATE TABLE #LogInfo;
  IF @@FETCH_STATUS <> 0 BREAK;
  SET @tsql_command= REPLACE(@tsql_template, N'?', @database_name);
  PRINT @tsql_command;
  EXECUTE(@tsql_command);
  INSERT INTO #VLFStats ([Database Name], [File ID], [# of VLFs], [Min VLF Size (MB)], [Avg VLF Size (MB)], [Max VLF Size (MB)], [Total VLF Size (MB)])
	SELECT @database_name, FileID, COUNT(*), MIN(FileSize)/1048576.0, AVG(FileSize)/1048576.0, MAX(FileSize)/1048576.0, SUM(FileSize)/1048576.0
	FROM #LogInfo GROUP BY FileID
END;

UPDATE #VLFStats SET
	[Size (MB)]		= f.Size,
	[Growth (MB)]	= f.Growth,
	IsPercent		= f.IsPercent
  FROM #VLFStats l INNER JOIN #FileInfo f ON (l.[Database Name] = f.db_name AND l.[File ID] = f.FileID);
DROP TABLE #FileInfo;
DROP TABLE #LogInfo;
GO

SELECT * FROM #VLFStats ORDER BY [# of VLFs] DESC, [Total VLF Size (MB)] DESC;
GO

/*
	USE [?];  
	EXECUTE master.DBAdmin.BackupTLOGs @db = '?';
	CHECKPOINT; DBCC SHRINKFILE(2,0)
	GO
**/